//
//  OWDBManager.m
//  MY_Book
//
//  Created by weiwang on 16/6/14.
//  Copyright © 2016年 王伟. All rights reserved.
//

#import "NEDBManager.h"
#import <FMDB/FMDB.h>
#import <sqlite3.h>

#import "NSURLEntity.h"

#define FIELD_COMMA(A)     [A stringByAppendingString:@","]

#define DB_DIR @"/Library/NE_Database"
#define DB_PATH  [DB_DIR stringByAppendingString: [NSString stringWithFormat:@"/%@", @"NENEWS_DB.sqlite"]]

#define NEWS_URLS_TABLE         @"NEWS_URLS_TABLE"

//#pragma mark- 数据库表各个字段
#define    TITLE                        @"TITLE"
#define    URLSTRING                    @"URLSTRING"
#define    REPLYURL                     @"REPLYURL"
#define    SORT                        @"SORT"
#define    ISCURRENT                    @"ISCURRENT"





@interface NEDBManager ()
@property(nonatomic, nonnull, strong)FMDatabaseQueue *dbQueue;
@end
@implementation NEDBManager

+(instancetype)shareInstance
{
    static NEDBManager *instance = nil;
    static dispatch_once_t predicate;
    dispatch_once(&predicate, ^{
        instance = [[self alloc] init];
        NSFileManager *fileManager = [NSFileManager defaultManager];
        BOOL b = [fileManager fileExistsAtPath:DB_DIR];
        if (b == NO) {
            [fileManager createDirectoryAtPath:[NSHomeDirectory() stringByAppendingPathComponent:DB_DIR] withIntermediateDirectories:YES attributes:nil error:nil];
        }
        instance.dbQueue = [FMDatabaseQueue databaseQueueWithPath:[NSHomeDirectory() stringByAppendingPathComponent:DB_PATH]];
        [instance _createTables];
    });
    return instance;
}


//#pragma mark- add增加
//-(void)insertBook:(ZZBook *)book cover:(BOOL)cover
//{
//    /**操作表：BookInfo**/
//    if ([self bookWithID:book.data.bookid]) {
//        if (cover) {        //存在且允许覆盖
//            [self updateBook:book];
//            for (ZZFile *file in book.files) {
//                file.bookid = book.data.bookid;
//                [self insertFile:file cover:YES];
//            }
//            return;
//        }else{          //存在但不允许覆盖
//            return;
//        }
//    }
//    
//    //不存在该记录
//    NSString *coverName = [book.data.coverUrl lastPathComponent];
//    //赋予个别字段初始值
//    book.data.coverPath = [Tools filePath:COVER_DIR, book.data.bookid , coverName, nil];
//    book.data.bookPath =  [Tools dirPath:READING_DIR, book.data.bookid, nil];
//    book.data.bookState = READY_TO_DOWNLOAD;
//    book.data.downloadTime = [NSDate date];
//    
//    NSMutableString *query    = [NSMutableString stringWithFormat:@"INSERT INTO %@ ", KBookInfoTableName];
//    NSMutableString *keys     = [NSMutableString stringWithFormat:@"("];
//    NSMutableString *values   = [NSMutableString stringWithFormat:@"("];
//    NSMutableArray *arguments = [NSMutableArray arrayWithCapacity:25];
//    
//    if (book.data.bookid) {
//        [keys appendString:FIELD_COMMA(BOOKID)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.bookid];
//    }
//    
//    if (book.data.typeName) {
//        [keys appendString:FIELD_COMMA(TYPENAME)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.typeName];
//    }
//    
//    if (book.data.resourceType) {
//        [keys appendString:FIELD_COMMA(RESOURCETYPE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:[NSNumber numberWithInt:book.data.resourceType]];
//    }
//    
//    if (book.data.name) {
//        [keys appendString:FIELD_COMMA(NAME)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.name];
//    }
//    
//    if (book.data.description) {
//        [keys appendString:FIELD_COMMA(DESCRIPTION)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.description];
//    }
//    
//    if (book.data.bookType) {
//        [keys appendString:FIELD_COMMA(BOOKTYPE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject: book.data.bookType];
//    }else{
//        [keys appendString:FIELD_COMMA(BOOKTYPE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject: @"NULL"];
//    }
//    
//    if (book.data.bookPath) {
//        [keys appendString:FIELD_COMMA(BOOKPATH)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.bookPath];
//    }
//    
//    if (book.data.coverPath) {
//        [keys appendString:FIELD_COMMA(COVERPATH)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:[NSString stringWithFormat:@"%@", book.data.coverPath]];
//    }
//    
//    if (book.data.author) {
//        [keys appendString:FIELD_COMMA(AUTHOR)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.author];
//    }
//    
//    if (book.data.bookState) {
//        [keys appendString:FIELD_COMMA(BOOKSTATE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:[NSString stringWithFormat:@"%d", book.data.bookState]];
//    }
//    
//    if (book.data.bookStateStr) {
//        [keys appendString:FIELD_COMMA(BOOKSTATESTR)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:[NSString stringWithFormat:@"%@", book.data.bookStateStr]];
//    }
//    
//    if (book.data.fileSize) {
//        [keys appendString:FIELD_COMMA(FILESIZE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:[NSString stringWithFormat:@"%lld",book.data.fileSize]];
//    }
//    
//    if (book.data.downloadedSize) {
//        [keys appendString:FIELD_COMMA(DOWNLOADEDSIZE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:[NSString stringWithFormat:@"%lld", book.data.downloadedSize]];
//    }
//    
//    if (book.data.scanUrl) {
//        [keys appendString:FIELD_COMMA(SCANURL)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.scanUrl];
//    }
//    
//    if (book.data.coverNative) {
//        [keys appendString:FIELD_COMMA(COVERNATIVE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.coverNative];
//    }
//    
//    if (book.data.coverUrl) {
//        [keys appendString:FIELD_COMMA(COVERURL)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.coverUrl];
//    }
//    
//    if (book.data.downloadTime) {
//        [keys appendString:FIELD_COMMA(DOWNLOADTIME)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.downloadTime];
//    }
//    
//    if (book.data.lastReadingTime) {
//        [keys appendString:FIELD_COMMA(LASTREADINGTIME)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.lastReadingTime];
//    }
//    
//    //期刊专有
//    if (book.data.period) {
//        [keys appendString:FIELD_COMMA(PERIOD)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.period];
//    }
//    if (book.data.price) {
//        [keys appendString:FIELD_COMMA(PRICE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.price];
//    }
//    if (book.data.tag) {
//        [keys appendString:FIELD_COMMA(TAG)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.tag];
//    }
//    
//       //报纸专有
//    if (book.data.updatetime) {
//        [keys appendString:FIELD_COMMA(UPDATETIME)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.updatetime];
//    }
//    if (book.data.region) {
//        [keys appendString:FIELD_COMMA(REGION)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.updatetime];
//    }
//    if (book.data.createtime) {
//        [keys appendString:FIELD_COMMA(CREATETIME)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:book.data.updatetime];
//    }
//
//    [keys appendString:@")"];
//    [values appendString:@")"];
//    [query appendFormat:@" %@ VALUES%@",
//     [keys stringByReplacingOccurrencesOfString:@",)"  withString:@")"],
//     [values stringByReplacingOccurrencesOfString:@",)" withString:@")"]];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        [db executeUpdate:query withArgumentsInArray:arguments];
//    }];
//    
//    for (ZZFile *file in book.files) {
//        file.bookid = book.data.bookid;
//        [self insertFile:file cover:NO];
//    }
//    return;
//}
//
//- (void)insertFile:(ZZFile *)file cover:(BOOL)cover
//{
//    if ([self fileWithID:file.fileid andBookID:file.bookid]) {
//        if (cover) {        //存在且允许覆盖
//            [self updateFile:file];
//            return;
//        }else{          //存在但不允许覆盖
//            return;
//        }
//    }
//    
//    ZZBook *book = [self bookWithID:file.bookid];
//    file.resourceType = book.data.resourceType;
//    //不存在该记录
//    NSString *fileName = [file.fileNative lastPathComponent];
//    NSString *coverName = [file.coverNative lastPathComponent];
//    //赋予个别字段初始值
//    file.filePath = [Tools filePath:READING_DIR, file.bookid, fileName, nil];
//    file.fileState = READY_TO_DOWNLOAD;
//    file.downloadTime = [NSDate date];
//    file.coverPath = [Tools filePath:COVER_DIR, file.bookid, coverName, nil];
//    
//    NSMutableString *query = [NSMutableString stringWithFormat:@"INSERT INTO %@ ", KChapterInfoTableName];
//    NSMutableString *keys = [NSMutableString stringWithFormat:@"("];
//    NSMutableString *values = [NSMutableString stringWithFormat:@"("];
//    NSMutableArray *arguments = [NSMutableArray arrayWithCapacity:20];
//    
//    if (file.bookid) {
//        [keys appendString:FIELD_COMMA(BOOKID)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.bookid];
//    }
//
//    if (file.fileid) {
//        [keys appendString:FIELD_COMMA(FILEID)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.fileid];
//    }
//
//    [keys appendString:FIELD_COMMA(SORT)];
//    [values appendString:FIELD_COMMA(@"?")];
//    [arguments addObject:[NSString stringWithFormat:@"%d", file.sort]];
//    
//    if (file.filePath) {
//        [keys appendString:FIELD_COMMA(FILEPATH)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.filePath];
//    }
//    
//    if (file.fileState) {
//        [keys appendString:FIELD_COMMA(FILESTATE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:[NSString stringWithFormat:@"%d", file.fileState]];
//    }
//    
//    if (file.fileStateStr) {
//        [keys appendString:FIELD_COMMA(FILESTATESTR)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.fileStateStr];
//    }
//    
//    if (file.title) {
//        [keys appendString:FIELD_COMMA(TITLE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.title];
//    }
//    
//    if (file.fileName) {
//        [keys appendString:FIELD_COMMA(FILENAME)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.fileName];
//    }
//    
//    if (file.filesize) {
//        [keys appendString:FIELD_COMMA(FILESIZE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.filesize];
//    }
//    
//    if (file.downloadTime) {
//        [keys appendString:FIELD_COMMA(DOWNLOADTIME)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.downloadTime];
//    }
//    
//    if (file.fileUrl) {
//        [keys appendString:FIELD_COMMA(FILEURL)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.fileUrl];
//    }
//    
//    if (file.fileNative) {
//        [keys appendString:FIELD_COMMA(FILENATIVE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.fileNative];
//    }
//    
//    if (file.userkey) {
//        [keys appendString:FIELD_COMMA(USERKEY)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.userkey];
//    }
//    
//    if (file.encrypttype) {
//        [keys appendString:FIELD_COMMA(ENCRYPTTYPE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.encrypttype];
//    }
//    
//    if (file.lastReadingTime) {
//        [keys appendString:FIELD_COMMA(LASTREADINGTIME)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.lastReadingTime];
//    }
//    
//    if (file.resourceType != 0) {
//        [keys appendString:FIELD_COMMA(RESOURCETYPE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:[NSNumber numberWithInteger:file.resourceType]];
//    }
//    
//    //期刊专有
//    if (file.edition) {
//        [keys appendString:FIELD_COMMA(EDITION)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.edition];
//    }
//    
//    if (file.magazine) {
//        [keys appendString:FIELD_COMMA(MAGAZINE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.magazine];
//    }
//    
//    if (file.mcoverNative) {
//        [keys appendString:FIELD_COMMA(MCOVERNATIVE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.mcoverNative];
//    }
//    
//    if (file.mcoverUrl) {
//        [keys appendString:FIELD_COMMA(MCOVERURL)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.mcoverUrl];
//    }
//    
//    if (file.pwd) {
//        [keys appendString:FIELD_COMMA(PWD)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.pwd];
//    }
//    if (file.mid) {
//        [keys appendString:FIELD_COMMA(MID)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.mid];
//    }
//    
//    //报纸专有
//    if (file.date) {
//        [keys appendString:FIELD_COMMA(DATE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.date];
//    }
//    if (file.pid) {
//        [keys appendString:FIELD_COMMA(PID)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.pid];
//    }
//    if (file.coverNative) {
//        [keys appendString:FIELD_COMMA(COVERNATIVE)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.coverNative];
//    }
//    if (file.coverUrl) {
//        [keys appendString:FIELD_COMMA(COVERURL)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.coverUrl];
//    }
//    
//    if (file.coverPath) {
//        [keys appendString:FIELD_COMMA(COVERPATH)];
//        [values appendString:FIELD_COMMA(@"?")];
//        [arguments addObject:file.coverPath];
//    }
//    
//    [keys appendString:@")"];
//    [values appendString:@")"];
//    [query appendFormat:@" %@ VALUES%@", [keys stringByReplacingOccurrencesOfString:@",)"  withString:@")"], [values stringByReplacingOccurrencesOfString:@",)" withString:@")"]];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        if ([db executeUpdate:query withArgumentsInArray:arguments]) {
//            NSLog(@"保存章节 %@ 成功", file.fileid);
//        }
//    }];
//}
//
//
//#pragma mark- 删
//- (void)_removeBookRocordById:(NSString *)bookId
//{
//    //删除'BookInfo'表中对应的book信息
//    NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ where BOOKID = '%@'", KBookInfoTableName, bookId];
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        if ([db executeUpdate:sql] == NO) {
//            NSLog(@"删除书籍：%@， 失败", bookId);
//        }
//    }];
//}
//
//- (void)removeFileById:(NSString *)fileId andBookId:(NSString *)bookid
//{
//
//     //删除文件对应路径及文件 包涵封面和书籍。
//    ZZFile *file = [self fileWithID:fileId andBookID:bookid];
//    if (file.coverPath && file.filePath) {
//        NSString *coverPath = [Tools absolutePath:file.coverPath];
//        NSString *filePath = [Tools absolutePath:file.filePath];
//        NSError *error = nil;
//        [[NSFileManager defaultManager] removeItemAtPath:coverPath error:nil];
//        if (error) {
//            NSLog(@"删除文件封面失败：%@", error);
//        }
//        [[NSFileManager defaultManager] removeItemAtPath:filePath  error:nil];
//        if (error) {
//            NSLog(@"删除文件失败：%@", error);
//        }
//    }
//    
//    //删除'BookInfo'表中对应的book信息
//    NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ where ", KChapterInfoTableName];
//    sql = [sql stringByAppendingFormat:@" upper(BOOKID)= upper('%@')", bookid];
//    sql = [sql stringByAppendingFormat:@" AND upper(FILEID)= upper('%@')", fileId];
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        if ([db executeUpdate:sql] == NO) {
//            NSLog(@"删除章节：%@， 失败", fileId);
//        }
//    }];
//}
//
//- (void)removeBookById:(NSString *)bookId
//{
//    ZZBook *book = [self bookWithID:bookId];
//    //获取cover路径
//    NSString *coverPath = [Tools absolutePath:book.data.coverPath];
//    //获取reading路径
//    NSString *bookPath = [Tools absolutePath:book.data.bookPath];;
//    
//    //删除'BookInfo'表中对应的book信息
//    [self _removeBookRocordById:bookId];
//
//    //删除所有章节
//    for (ZZFile *file in book.files) {
//        [self removeFileById:file.fileid andBookId:book.data.bookid];
//    }
//    
//    NSError *error = nil;
//    //删除文件对应路径及文件 包涵封面和书籍。
//    [[NSFileManager defaultManager] removeItemAtPath:coverPath error:&error];
//    if (error) {
//        NSLog(@"删除封面失败：%@", error);
//    }
//    
//    [[NSFileManager defaultManager] removeItemAtPath:bookPath  error:&error];
//    if (error) {
//        NSLog(@"删除book失败：%@", error);
//    }
//    
//}
//
//#pragma mark- 改
//-(BOOL)updateBook:(ZZBook *)book
//{
//    if (book == nil) {
//        NSLog(@"更新数据，book参数为空");
//        return NO;
//    }
//    
//    __block BOOL b = NO;
//    
//    NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET ", KBookInfoTableName];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@" RESOURCETYPE = '%d'", book.data.resourceType]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", BOOKTYPE = '%@'", book.data.bookType]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", TYPENAME = '%@'", book.data.typeName]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", ADDTIME = '%@'", book.data.addtime]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", AUTHOR = '%@'", book.data.author]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", CLCCATEGORY = '%d'", book.data.clccategory]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", COMCATEGORY = '%d'", book.data.comcategory]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", COVERNATIVE = '%@'", book.data.coverNative]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", COVERURL = '%@'", book.data.coverUrl]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", BOOKID = '%@'", book.data.bookid]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", ISBN = '%@'", book.data.isbn]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", ISENCRYPT = '%d'", book.data.isencrypt]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", NAME = '%@'", book.data.name]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", NAMECHAR = '%@'", book.data.namechar]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", PUBLISHER = '%@'", book.data.publisher]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", SORT = '%d'", book.data.sort]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", FILESIZE = '%lld'", book.data.fileSize]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", DOWNLOADEDSIZE = '%lld'", book.data.downloadedSize]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", SCANURL = '%@'", book.data.scanUrl]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", DOWNLOADTIME = '%@'", book.data.downloadTime]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", LASTREADINGTIME = '%@'", book.data.lastReadingTime]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", BOOKPATH = '%@'", book.data.bookPath]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", COVERPATH = '%@'", book.data.coverPath]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", BOOKSTATE = '%d'", book.data.bookState]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", BOOKSTATESTR = '%@'", book.data.bookStateStr]];
////期刊专有
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", PERIOD = '%@'", book.data.period]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", PRICE = '%@'", book.data.price]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", TAG = '%@'", book.data.tag]];
////报纸专有
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", UPDATETIME = '%@'", book.data.updatetime]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", REGION = '%@'", book.data.region]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", CREATETIME = '%@'", book.data.createtime]];
//    sql = [sql stringByAppendingFormat:@" WHERE BOOKID = '%@'", book.data.bookid];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        b = [db executeUpdate:sql];
//    }];
//    return b;
//}
//
//-(BOOL)updateFile:(ZZFile *)file
//{
//    if (file == nil) {
//        NSLog(@"更新数据，file参数为空");
//        return NO;
//    }
//    __block BOOL b = NO;
//    
//    NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET ", KChapterInfoTableName];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@" ENCRYPTTYPE = '%@'", file.encrypttype]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", FILENATIVE = '%@'", file.fileNative]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", FILEURL = '%@'", file.fileUrl]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", SORT = '%d'", file.sort]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", FILESIZE = '%@'", file.filesize]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", FILEID = '%@'", file.fileid]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", BOOKID = '%@'", file.bookid]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", TITLE = '%@'", file.title]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", USERKEY = '%@'", file.userkey]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", FILEPATH = '%@'", file.filePath]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", DOWNLOADTIME = '%@'", file.downloadTime]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", LASTREADINGTIME = '%@'", file.lastReadingTime]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", FILESTATE = '%@'", file.filesize]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", FILESTATESTR = '%@'", file.fileStateStr]];
////期刊专有
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", EDITION = '%@'", file.edition]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", MAGAZINE = '%@'", file.magazine]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", MCOVERNATIVE = '%@'", file.mcoverNative]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", MCOVERURL = '%@'", file.mcoverUrl]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", PWD = '%@'", file.pwd]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", MID = '%@'", file.mid]];
////报纸专有
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", DATE = '%@'", file.date]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", PID = '%@'", file.pid]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", COVERNATIVE = '%@'", file.coverNative]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", COVERURL = '%@'", file.coverUrl]];
//    
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", COVERPATH = '%@'", file.coverPath]];
//    
//    sql = [sql stringByAppendingFormat:@" WHERE BOOKID = '%@'", file.bookid];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        b = [db executeUpdate:sql];
//    }];
//    
//    return b;
//}
//
//-(BOOL)updateFileState:(int)state WithFileID:(NSString *)fileid andBookID:(NSString *)bookid
//{
//    ZZFile *file = [self fileWithID:fileid andBookID:bookid];
//    if (file == nil) {
//        NSLog(@"更新数据，file参数为空");
//        return NO;
//    }
//    
//    __block BOOL b = NO;
//    
//    file.fileState = state;
//    NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET ", KChapterInfoTableName];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@" FILESTATE = '%d'", file.fileState]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", FILESTATESTR = '%@'", file.fileStateStr]];
//    sql = [sql stringByAppendingFormat:@" WHERE BOOKID = '%@' AND FILEID = '%@'", file.bookid, file.fileid];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        b = [db executeUpdate:sql];
//    }];
//    
//    //同时跟新所属book的状态
//    //报刊，书籍，期刊 因为book中仅仅只含有一个file，所以 book的各种状态与file一致。
//    //听书时，某一file状态"下载完成", 不代表整本听书book对象"下载完成"
//    ZZBook *book = [self bookWithID:bookid];
//    
//    //非听书类型
//    if (book.data.resourceType != RESOURCETYPE_BOOK || [book.data.bookType isEqualToString:BOOKTYPE_BOOK]) {
//        //同样跟新book状态
//        [self updateBookState:state withBookID:bookid];
//    }else{
//        //听书类型，状态"下载失败"， “下载中”, book状态与file一致
//        //         状态"下载成功"， book需要检查所有file的状态
//        
//        if(state != DOWNLOAD_SUCCESS)
//        {
//            [self updateBookState:state withBookID:bookid];
//        }
//        else
//        {
//            if([self isAllFilesDownloadSuccessWithBookId:bookid] == 0)
//            {
//                [self updateBookState:DOWNLOAD_SUCCESS withBookID:bookid];
//            }
//        }
//    }
//    
//    return b;
//}
//
//-(BOOL)updateBookState:(int)state withBookID:(NSString *)bookid
//{
//    ZZBook *book = [self bookWithID:bookid];
//    if (book == nil) {
//        NSLog(@"更新数据，file参数为空");
//        return NO;
//    }
//    
//    __block BOOL b = NO;
//    book.data.bookState = state;
//    NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET ", KBookInfoTableName];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@" BOOKSTATE = '%d'", state]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", BOOKSTATESTR = '%@'", book.data.bookStateStr]];
//    
//    sql = [sql stringByAppendingFormat:@" WHERE BOOKID = '%@'", book.data.bookid];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        b = [db executeUpdate:sql];
//    }];
//    
//    return b;
//}
//
////跟新所有下载中书籍的状态为下载失败
//-(void)updateAllDownloadingFilesToFailedState
//{
//    NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET ", KChapterInfoTableName];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@" FILESTATE = '%d'", DOWNLOAD_FAILED]];
//    sql = [sql stringByAppendingString:[NSString stringWithFormat:@", FILESTATESTR = '%@'", @"下载失败"]];
//    sql = [sql stringByAppendingFormat:@" WHERE FILESTATE = '%d' ", DOWNLOADING];
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        [db executeUpdate:sql];
//    }];
//}
//
//
//#pragma mark- 查
//-(ZZBook *)bookWithID:(NSString *)bookid
//{
//    __block ZZBook *book = nil;
//    NSString *query = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE ", KBookInfoTableName];
//    query = [query stringByAppendingFormat:@" upper(BOOKID)= upper('%@')", bookid];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        FMResultSet *rs = [db executeQuery:query];
//        while ([rs next])
//        {
//            book = [self _generateBookForm:rs];
//        }
//        [rs close];
//    }];
//    
//    book.files = [self filesByBookId:bookid];
//    return book;
//}
//
//
//
//-(ZZFile *)fileWithID:(NSString *)fileID andBookID:(NSString *)bookid
//{
//    __block ZZFile *file = nil;
//    NSString *query = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE ", KChapterInfoTableName];
//    query = [query stringByAppendingFormat:@" upper(BOOKID)= upper('%@')", bookid];
//    query = [query stringByAppendingFormat:@" AND upper(FILEID)= upper('%@')", fileID];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        FMResultSet *rs = [db executeQuery:query];
//        while ([rs next])
//        {
//            file = [self _generateFileFrom:rs];
//            
//        }
//        [rs close];
//    }];
//    
//    return file;
//}
//
//
//-(int)isAllFilesDownloadSuccessWithBookId:(NSString *)bookid
//{
//    __block int count = 0;
//    NSString *query = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ WHERE ", KChapterInfoTableName];
//    query = [query stringByAppendingFormat:@" upper(BOOKID)= upper('%@')", bookid];
//    query = [query stringByAppendingFormat:@" AND upper(FILESTATE)!= upper('%d')", DOWNLOAD_SUCCESS];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        FMResultSet *rs = [db executeQuery:query];
//        if([rs next])
//        {
//            count = [rs intForColumn:0];
//        }
//        [rs close];
//    }];
//    
//    return count;
//}
//
////查询所有在库中纪录的书籍
//-(NSArray *)allBooksWithResourceType:(int)type
//{
//    __block ZZBook *book = nil;
//    NSMutableArray *array = [NSMutableArray arrayWithCapacity:0];
//    NSString *query = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE ", KBookInfoTableName];
//    query = [query stringByAppendingFormat:@" RESOURCETYPE= '%d'", type];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        FMResultSet *rs = [db executeQuery:query];
//        while ([rs next])
//        {
//            book = [self _generateBookForm:rs];
//            [array addObject:book];
//        }
//        [rs close];
//    }];
//    
//    for (ZZBook *book in array) {
//        NSMutableArray *files = [self filesByBookId:book.data.bookid];
//        book.files = files;
//    }
//    
//    return array;
//}
//
////查询所有在库中纪录的书籍
//-(NSArray *)allFilesWithResourceType:(int)type
//{
//    __block ZZFile *file = nil;
//    NSMutableArray *array = [NSMutableArray arrayWithCapacity:0];
//    NSString *query = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE ", KChapterInfoTableName];
//    query = [query stringByAppendingFormat:@" RESOURCETYPE= '%d'", type];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        FMResultSet *rs = [db executeQuery:query];
//        while ([rs next])
//        {
//            file = [self _generateFileFrom:rs];
//            [array addObject:file];
//        }
//        [rs close];
//    }];
//    
//
//    
//    return array;
//}
//
//
//- (NSMutableArray *)filesByBookId:(NSString *)bookId
//{
//    __block ZZFile *file = nil;
//    
//    NSMutableArray *chapterInfos = [[NSMutableArray alloc] init];
//    NSString *query = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE", KChapterInfoTableName];
//    query = [query stringByAppendingFormat:@" upper(BOOKID)= upper('%@')", bookId];
//    
//    [_dbQueue inDatabase:^(FMDatabase *db){
//        FMResultSet *rs = [db executeQuery:query];
//        while ([rs next])
//        {
//            file = [self _generateFileFrom:rs];
//            [chapterInfos addObject:file];
//        }
//        [rs close];
//    }];
//    
//    return chapterInfos;
//}
//


#pragma mark- 创建数据库表
-(void)_createTables
{
    //创建数据库表：NEWS_URLS_TABLE, AUTOINCREMENT
    NSString *sql = [NSString stringWithFormat:
                     @"CREATE TABLE IF NOT EXISTS %@ (\
                     SORT INTEGER PRIMARY KEY NOT NULL, \
                     ISCURRENT INTEGER,   \
                     TITLE VARCHAR(255), \
                     URLSTRING VARCHAR(255), \
                     REPLYURL VARCHAR(255))",
                     NEWS_URLS_TABLE];

    [self.dbQueue inDatabase:^(FMDatabase *db) {
        BOOL res = [db executeUpdate:sql];
        if (!res) {
            NSLog(@"创建数据库：%@失败", sql);
        } else {
            NSLog(@"创建数据库");
        }
    }];
    
    [self _instertURLEntitys]; //不可以写在inDatabase块中，否则报错：Assertion failed: (currentSyncQueue != self && "inDatabase: was called reentrantly on the same queue, which would lead to a deadlock"),
}

-(void)_instertURLEntitys
{
        NSString *plistPath = [[NSBundle mainBundle] pathForResource:@"newsUrls" ofType:@"plist"];
        NSArray *array = [NSArray arrayWithContentsOfFile:plistPath];
    
        for (NSDictionary *dic in array) {

        NSMutableString *query    = [NSMutableString stringWithFormat:@"INSERT INTO %@ ", NEWS_URLS_TABLE];
        NSMutableString *keys     = [NSMutableString stringWithFormat:@"("];
        NSMutableString *values   = [NSMutableString stringWithFormat:@"("];
        NSMutableArray *arguments = [NSMutableArray arrayWithCapacity:6];
            
        NSURLEntity *entity = [NSURLEntity mj_objectWithKeyValues:dic];
        
        if (entity.title) {
            [keys appendString:FIELD_COMMA(TITLE)];
            [values appendString:FIELD_COMMA(@"?")];
            [arguments addObject:entity.title];
        }
        
        if (entity.urlString) {
            [keys appendString:FIELD_COMMA(URLSTRING)];
            [values appendString:FIELD_COMMA(@"?")];
            [arguments addObject:entity.urlString];
        }
        
        if (entity.replyUrl) {
            [keys appendString:FIELD_COMMA(REPLYURL)];
            [values appendString:FIELD_COMMA(@"?")];
            [arguments addObject:entity.replyUrl];
        }
        
        if (entity.isCurrent) {
            [keys appendString:FIELD_COMMA(ISCURRENT)];
            [values appendString:FIELD_COMMA(@"?")];
            [arguments addObject:[NSNumber numberWithBool:entity.isCurrent]];
        }
        
        if (entity.sort >= 0) {
            [keys appendString:FIELD_COMMA(SORT)];
            [values appendString:FIELD_COMMA(@"?")];
            [arguments addObject:[NSNumber numberWithInteger:entity.sort]];
        }
        
        [keys appendString:@")"];
        [values appendString:@")"];
        [query appendFormat:@" %@ VALUES%@",
         [keys stringByReplacingOccurrencesOfString:@",)"  withString:@")"],
         [values stringByReplacingOccurrencesOfString:@",)" withString:@")"]];
            
        [self.dbQueue inDatabase:^(FMDatabase *db){
            [db executeUpdate:query withArgumentsInArray:arguments];
        }];
        
        }
       return;
}

-(NSArray *)allURLEntityShowed:(BOOL)show
{
    __block NSURLEntity *entity = nil;
    NSMutableArray *array = [NSMutableArray arrayWithCapacity:0];
    NSString *query = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE ", NEWS_URLS_TABLE];
    query = [query stringByAppendingFormat:@" ISCURRENT = '%d'", show];
    
    [_dbQueue inDatabase:^(FMDatabase *db){
        FMResultSet *rs = [db executeQuery:query];
        while ([rs next])
        {
            entity = [self _generateURLEntityFrom:rs];
            [array addObject:entity];
        }
        [rs close];
    }];
    return array;
}

-(NSURLEntity *)_generateURLEntityFrom:(FMResultSet *)rs
{
    NSURLEntity *entity = [[NSURLEntity alloc] init];
    entity.title = [rs stringForColumn:TITLE];
    entity.urlString = [rs stringForColumn:URLSTRING];
    entity.replyUrl = [rs stringForColumn:REPLYURL];
    entity.sort = [rs intForColumn:SORT];
    entity.isCurrent = [rs intForColumn:ISCURRENT];
    
    return entity;
}



//
//-(ZZFile *)_generateFileFrom:(FMResultSet *)rs
//{
//    ZZFile *chapter = nil;
//    chapter = [[ZZFile alloc] init];
//    chapter.bookid = [rs stringForColumn:@"BOOKID"];
//    chapter.fileid = [rs stringForColumn:@"FILEID"];
//    chapter.fileName = [rs stringForColumn:@"FILENAME"];
//    chapter.encrypttype = [rs stringForColumn:@"ENCRYPTTYPE"];
//    chapter.userkey = [rs stringForColumn:@"USERKEY"];
//    chapter.title = [rs stringForColumn:@"TITLE"];
//    chapter.filePath = [rs stringForColumn:@"FILEPATH"];
//    chapter.fileState = [rs intForColumn:@"FILESTATE"];
//    chapter.filesize = [rs stringForColumn:@"FILESIZE"];
//    chapter.downloadTime = [rs dateForColumn:@"DOWNLOADTIME"];
//    chapter.fileNative = [rs stringForColumn:@"FILENATIVE"];
//    chapter.fileUrl = [rs stringForColumn:@"FILEURL"];
//    chapter.downloadTime = [rs objectForColumnName:@"DOWNLOADTIME"];
//    chapter.lastReadingTime = [rs objectForColumnName:@"LASTREADINGTIME"];
//    chapter.resourceType = [rs intForColumn:@"RESOURCETYPE"];
//    
////期刊专有
//    chapter.edition = [rs stringForColumn:@"EDITION"];
//    chapter.magazine = [rs stringForColumn:@"MAGAZINE"];
//    chapter.mcoverNative = [rs stringForColumn:@"MCOVERNATIVE"];
//    chapter.mcoverUrl = [rs stringForColumn:@"MCOVERURL"];
//    chapter.pwd = [rs stringForColumn:@"PWD"];
//    chapter.mid = [rs stringForColumn:@"MID"];
//    
////报纸专有
//    chapter.date = [rs stringForColumn:@"DATE"];
//    chapter.pid = [rs stringForColumn:@"PID"];
//    chapter.coverPath = [rs stringForColumn:@"COVERPATH"];
//    chapter.coverNative = [rs stringForColumn:@"COVERNATIVE"];
//    chapter.coverUrl = [rs stringForColumn:@"COVERURL"];
//    
//    return chapter;
//}

@end
